"""
    工具模块
"""
import pymysql
from queue import Queue
from threading import Lock, Thread
import time
from pyboot import configuration_properties

"""
    数据库工具类
"""


@configuration_properties("mysql")
class MysqlConfig:
    user = None
    password = None
    database = None
    host = "127.0.0.1"
    port = 3306
    pool_size = 5
    prefix = ""
    # 数据库连接心跳检测
    heartbeat_interval = 30


mysql_config = MysqlConfig()


class ConnectionPool:
    def __init__(self):
        self.host = mysql_config.host
        self.port = mysql_config.port
        self.user = mysql_config.user
        self.password = mysql_config.password
        self.database = mysql_config.database
        self.pool_size = mysql_config.pool_size
        self.connections = Queue(maxsize=self.pool_size)
        self.lock = Lock()
        self.heartbeat_interval = mysql_config.heartbeat_interval
        self.initialize_pool()
        self.start_heartbeat()

    def initialize_pool(self):
        for _ in range(self.pool_size):
            connection = self.create_connection()
            self.connections.put(connection)

    def create_connection(self):
        return pymysql.connect(host=self.host, port=self.port,
                               user=self.user, password=self.password,
                               database=self.database)

    def get_connection(self):
        return self.connections.get()

    def release_connection(self, connection):
        self.connections.put(connection)

    def start_heartbeat(self):
        heartbeat_thread = Thread(target=self.send_heartbeat_signal)
        heartbeat_thread.daemon = True
        heartbeat_thread.start()

    def send_heartbeat_signal(self):
        while True:
            try:
                conn = self.get_connection()
                conn.ping(reconnect=True)
                self.release_connection(conn)

            except pymysql.err.OperationalError:
                conn = self.create_connection()
                self.release_connection(conn)

            time.sleep(self.heartbeat_interval)


# 只有配置了user才注入
if mysql_config.user is not None:
    # 初始化数据库连接池
    mysql_pool = ConnectionPool()


    class Db:
        # 假设配置文件中存储前缀信息
        config = {
            'prefix': mysql_config.prefix
        }

        def __init__(self, table_name):
            self.table_name = f"{table_name}"
            self.conditions = []
            self.order_by = ""
            self.fields = "*"
            self.params = []

        @staticmethod
        def table(table_name):
            return Db(f"{table_name}")

        @staticmethod
        def name(table_name):
            prefix = Db.config.get('prefix', '')
            return Db(f"{prefix}{table_name}")

        def get_primary_key(self):
            connection = mysql_pool.get_connection()
            try:
                with connection.cursor() as cursor:
                    # 查询表的主键字段名
                    cursor.execute(
                        f"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = '{self.table_name}' AND CONSTRAINT_NAME = 'PRIMARY'")
                    primary_keys = cursor.fetchall()
                    # 打印主键字段名
                    if primary_keys:
                        for key in primary_keys:
                            pass
                        return primary_keys  # 返回主键字段名给调用者
                    else:
                        return None  # 如果没有找到主键，则返回 None
            finally:
                # 关闭数据库连接
                connection.close()

        def where(self, field=None, oper='=', value=None, array=None):
            if array:
                if not isinstance(array[0], list):  # 检查是否是一维数组
                    array = [array]  # 将一维数组转换为二维数组
                for condition in array:
                    condition_str = ' '.join(condition[:-1])  # 获取条件字符串，排除最后一个元素（参数）
                    if self.conditions:
                        self.conditions.append(f"AND {condition_str} %s")
                    else:
                        self.conditions.append(f"{condition_str} %s")
                    self.params.append(condition[-1])  # 添加参数到列表中
            elif field and value is not None:
                if self.conditions:  # 如果已经有其他条件，则添加 AND 连接
                    self.conditions.append(f"AND {field} {oper} %s")
                else:
                    self.conditions.append(f"{field} {oper} %s")
                self.params.append(value)  # 将整数值转换为字符串
            return self

        def orderby_asc(self, field_name):
            self.order_by = f"ORDER BY {field_name} ASC"
            return self

        def orderby_desc(self, field_name):
            self.order_by = f"ORDER BY {field_name} DESC"
            return self

        def includes(self, fields):
            self.fields = ", ".join(fields)
            return self

        def excludes(self, fields):
            # Assuming we fetch all columns first, and exclude the given ones
            # This requires knowing the table structure beforehand
            all_columns = self.get_all_columns()
            self.fields = ", ".join([col for col in all_columns if col not in fields])
            return self

        def get_all_columns(self):
            connection = mysql_pool.get_connection()
            try:
                with connection.cursor() as cursor:
                    cursor.execute(f"SHOW COLUMNS FROM {self.table_name}")
                    columns = [column[0] for column in cursor.fetchall()]
            finally:
                mysql_pool.release_connection(connection)
            return columns

        def build_query(self, limit=None):
            query = f"SELECT {self.fields} FROM {self.table_name}"
            if self.conditions:
                query += " WHERE " + " ".join(self.conditions)
            if self.order_by:
                query += f" {self.order_by}"
            if limit:
                query += f" LIMIT {limit}"
            return query

        def get(self):
            query = self.build_query(limit=1)
            connection = mysql_pool.get_connection()
            try:
                with connection.cursor(pymysql.cursors.DictCursor) as cursor:
                    cursor.execute(query, self.params)
                    result = cursor.fetchone()
            finally:
                mysql_pool.release_connection(connection)
            return result if result else None

        def list(self):
            query = self.build_query()
            print(query)
            connection = mysql_pool.get_connection()
            try:
                with connection.cursor(pymysql.cursors.DictCursor) as cursor:
                    cursor.execute(query, self.params)
                    result = cursor.fetchall()
            finally:
                mysql_pool.release_connection(connection)
            return result if result else []

        def count(self):
            query = f"SELECT COUNT(*) as count FROM {self.table_name}"
            if self.conditions:
                query += " WHERE " + " ".join(self.conditions)
            connection = mysql_pool.get_connection()
            try:
                with connection.cursor(pymysql.cursors.DictCursor) as cursor:
                    cursor.execute(query, self.params)
                    result = cursor.fetchone()
                    count = result['count'] if result else 0
            finally:
                mysql_pool.release_connection(connection)
            return count

        @staticmethod
        def sql(query, params=None):
            connection = mysql_pool.get_connection()
            try:
                with connection.cursor(pymysql.cursors.DictCursor) as cursor:
                    cursor.execute(query, params)
                    result = cursor.fetchall()
            finally:
                mysql_pool.release_connection(connection)
            return result if result else []

        def update(self, data):
            if isinstance(data, dict):
                data = [data]

            affected_rows = 0
            primary_key = self.get_primary_key()
            connection = mysql_pool.get_connection()

            try:
                with connection.cursor() as cursor:
                    for item in data:
                        # 构建 SET 子句，排除主键字段
                        set_clauses = ', '.join([f"{key} = %s" for key in item.keys() if key != primary_key[0][0]])
                        # 使用主键字段名构建 WHERE 子句
                        query = f"UPDATE {self.table_name} SET {set_clauses} WHERE {primary_key[0][0]} = %s"
                        params = [item[key] for key in item.keys() if key != primary_key[0][0]]
                        params.append(item[primary_key[0][0]])  # 添加主键值到参数列表
                        affected_rows += cursor.execute(query, params)
                    connection.commit()
            finally:
                mysql_pool.release_connection(connection)

            return affected_rows

        def save(self, data):
            if isinstance(data, dict):
                data = [data]

            affected_rows = 0
            primary_key = self.get_primary_key()
            connection = mysql_pool.get_connection()

            try:
                with connection.cursor() as cursor:
                    for item in data:
                        keys = ', '.join(item.keys())
                        placeholders = ', '.join(['%s'] * len(item))
                        values = list(item.values())
                        query = f"INSERT INTO {self.table_name} ({keys}) VALUES ({placeholders})"
                        affected_rows += cursor.execute(query, values)
                    connection.commit()
            finally:
                mysql_pool.release_connection(connection)

            return affected_rows

        def delete_by_ids(self, ids):
            if isinstance(ids, int) or isinstance(ids, str):
                ids = [ids]
            elif isinstance(ids, list):
                ids = [str(temp_id) for temp_id in ids]

            connection = mysql_pool.get_connection()
            try:
                with connection.cursor() as cursor:
                    query = f"DELETE FROM {self.table_name} WHERE {self.get_primary_key()[0][0]} IN ({','.join(['%s'] * len(ids))})"
                    affected_rows = cursor.execute(query, ids)
                    connection.commit()
            finally:
                mysql_pool.release_connection(connection)
            return affected_rows
